package com.example.ssss.data;

import android.util.Log;

import com.example.ssss.entity.Behave;
import com.example.ssss.entity.ContactInfo;
import com.example.ssss.entity.History;
import com.example.ssss.entity.LNH;
import com.example.ssss.entity.HistoryInfo;
import com.example.ssss.entity.HistoryResponse;

import java.sql.Array;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Arrays;

public class MysqlOperation {
    public UserDao userDao;
    public HeartDao heartDao;
    public HistoryDao historyDao;
    public BehaveDao behaveDao;
    public WearDao wearDao;
//    public DataAnalysis dataAnalysis;
    public UserHistoryDao userHistoryDao;
    public ContactDao contactDao;

    public static class DBUtils {

        private final static String driver = "com.mysql.jdbc.Driver";
        private final static String url = "jdbc:mysql://123.57.214.240:3316/SSSS?useUnicode=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true&useSSL=false";
        private final static String username = "root";
        private final static String password = "Root2022@!";

        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        {
            try {
                Class.forName(driver);
            } catch (ClassNotFoundException e) {
                System.out.println("加载驱动错误");
            }
        }


        //2. 获取连接
        public static Connection getConnect() throws Exception {
            return DriverManager.getConnection(url, username, password);
        }

        //3. 释放连接资源

        public static void release(Connection conn, Statement st, ResultSet rs) throws Exception {
            if (rs != null) {
                rs.close();
            }
            if (st != null) {
                st.close();
            }
            if (conn != null) {
                conn.close();
            }

        }

    }


    public static class UserHistoryDao{
        public void insert(int historyid,String userid) throws Exception {
            Connection conn = DBUtils.getConnect();
//                state = conn.createStatement();
            String sql = "insert into  history_list  values (?,?);";
//                rs = state.executeQuery(sql);
            PreparedStatement pstm = conn.prepareStatement(sql);
            //通过setString给4个问好赋值，下面的course_id，user_id，course_time，us_job_id都是已有值的变量，不要误会了
            pstm.setString(2, userid);
            pstm.setString(1, historyid+"");
            //执行更新数据库
            pstm.executeUpdate();
            //关闭链接
            conn.close();
            //关闭访问
            pstm.close();
        }

    }

    public static class UserDao {

        public String selectpassword(String userid)throws Exception {
            Connection conn = null;
            Statement state = null;
            ResultSet rs = null;
            String password="";
            try {
                conn = DBUtils.getConnect();
                state = conn.createStatement();
                String sql="select password from user where userid='"+userid+"'";

                rs = state.executeQuery(sql);

                while (rs.next()) {
                    password=rs.getString(1);
                    break;
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                DBUtils.release(conn, state, rs);
            }
            return password;
        }
        public String selectHistoryid(int index,String userid)throws Exception {
            Connection conn = null;
            Statement state = null;
            ResultSet rs = null;
            String historyid="";
            try {
                conn = DBUtils.getConnect();
                state = conn.createStatement();
                String sql="select * from history_list where userid='"+userid+"' order by historyid desc";

                rs = state.executeQuery(sql);
                int k=0;

                while (rs.next()) {
                    if(k++==index+1)break;
                    historyid=rs.getString(1);
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                DBUtils.release(conn, state, rs);
            }
            return historyid;
        }

        //查完用户对应的第index条历史记录id,根据这个id抽取详细历史记录数据
        public HistoryResponse select(int index,String userid) throws Exception {
            String historyid=selectHistoryid(index,userid);
            Connection conn = null;
            Statement state = null;
            ResultSet rs = null;
            int p=0;
            try {
                conn = DBUtils.getConnect();
                state = conn.createStatement();
                String sql = "select count(*) from history_detail where historyid='"+historyid+"'";
                rs = state.executeQuery(sql);
                while (rs.next()) {
                    p=Integer.parseInt(rs.getString(1));
                }

            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                DBUtils.release(conn, state, rs);
            }
            if(p==0){
                HistoryResponse historyResponse1=new HistoryResponse();
                historyResponse1.setStatus("暂无");
                historyResponse1.setStartAndEndTime("暂无");
                historyResponse1.setHeart_avg("暂无");
                historyResponse1.setConcentration("暂无");
                historyResponse1.setSysPres_avg("暂无");
                historyResponse1.setDiaPres_avg("暂无");
                historyResponse1.setDangerousBehave("暂无");
                historyResponse1.setOxy_avg("暂无");
                historyResponse1.setDrivingTemp("暂无");
                historyResponse1.setDrivingWet("暂无");
                historyResponse1.setDrivingCO2("暂无");
                historyResponse1.setDrivingTVOC("暂无");
                historyResponse1.setScore("暂无");
                return  historyResponse1;
            }
            Connection conn1 = null;
            Statement state1 = null;
            ResultSet rs1 = null;
            HistoryResponse historyResponse1=new HistoryResponse();
            try {
                conn1 = DBUtils.getConnect();
                state1 = conn1.createStatement();
                String sql = "select * from history_detail where historyid='"+historyid+"'";
                rs1 = state1.executeQuery(sql);
                while (rs1.next()) {
                        historyResponse1.setStatus(rs1.getString(2));
                        historyResponse1.setStartAndEndTime(rs1.getString(3));
                        historyResponse1.setHeart_avg(rs1.getString(4));
                        historyResponse1.setConcentration(rs1.getString(5));
                        historyResponse1.setSysPres_avg(rs1.getString(6));
                        historyResponse1.setDiaPres_avg(rs1.getString(7));
                        historyResponse1.setDangerousBehave(rs1.getString(8));
                        historyResponse1.setOxy_avg(rs1.getString(9));
                        historyResponse1.setDrivingTemp(rs1.getString(10));
                        historyResponse1.setDrivingWet(rs1.getString(11));
                        historyResponse1.setDrivingCO2(rs1.getString(12));
                        historyResponse1.setDrivingTVOC(rs1.getString(13));
                        historyResponse1.setScore(rs1.getString(15));
                }

            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                DBUtils.release(conn1, state1, rs1);
            }
            return historyResponse1;
        }


    }

    public static class HeartDao {

        //查询用户的方法
        public LNH select(String table, int index,String userid) throws Exception {
            Connection conn = null;
            Statement state = null;
            ResultSet rs = null;
            int[] data ;
            int[] data1=new int[1005];
            int[] data2=new int[1005];
            UserDao userDao=new UserDao();

            String historyid=userDao.selectHistoryid(index,userid);

            int k=0;int highnum=0,normalnum=0,lownum=0;
            LNH LNH =new LNH();
            LNH.setType(table);
            try {

                if (LNH.getType().equals("heart")) {
                    conn = DBUtils.getConnect();
                    state = conn.createStatement();
                    String sql = "select * from " + table + " where historyid='" + historyid + "'";
                    rs = state.executeQuery(sql);
                    while (rs.next()) {
                        data1[k++]= (int)Double.parseDouble(rs.getString(2));
                        if(data1[k-1]>=100){
                            highnum++;
                        }
                        else if(data1[k-1]<=60){
                            lownum++;
                        }else{
                            normalnum++;
                        }

                        //System.out.println(rs.getString(1) + " " + rs.getString(2));//就是输出第一列和第二列的值
                    }
                    data=new int[k];
                    System.arraycopy(data1, 0, data, 0, k);
                    LNH.setData(data);
                }else if(LNH.getType().equals("oxy")){
                    conn = DBUtils.getConnect();
                    state = conn.createStatement();
                    String sql = "select * from " + table+" where historyid='"+historyid+"'";
                    rs = state.executeQuery(sql);
                    while (rs.next()) {
                        data1[k++]= (int)Double.parseDouble(rs.getString(2));
                        if(data1[k-1]>=95){
                            normalnum++;
                        }
                       else{
                            lownum++;
                        }

                        //System.out.println(rs.getString(1) + " " + rs.getString(2));//就是输出第一列和第二列的值
                    }
                    data=new int[k];
                    if (k >= 0) System.arraycopy(data1, 0, data, 0, k);
                    LNH.setData(data);
                }else {
                    conn = DBUtils.getConnect();
                    state = conn.createStatement();
                    String sql = "select * from diaPress where historyid='"+historyid+"'";
                    rs = state.executeQuery(sql);
                    while (rs.next()) {
                        data1[k++]= (int)Double.parseDouble(rs.getString(2));
                        if(data1[k-1]>=60&&data1[k-1]<=90){
                            normalnum++;
                        }
                        else if(data1[k-1]<60){
                            lownum++;
                        }else{
                            highnum++;
                        }
                    }
                    data=new int[k];
                    if (k >= 0) System.arraycopy(data1, 0, data, 0, k);
                    LNH.setData(data);
                    Connection conn1 = DBUtils.getConnect();
                    Statement state1 = conn1.createStatement();

                    sql = "select * from sysPress where historyid='"+historyid+"'";
                    ResultSet rs1 = state1.executeQuery(sql);
                    k=0;
                    while (rs1.next()) {
                        data2[k++]= (int)Double.parseDouble(rs1.getString(2));
                        if(data2[k-1]>=90&&data2[k-1]<140){
                            normalnum++;
                        }
                        else if(data2[k-1]<90){
                            lownum++;
                        }else{
                            highnum++;
                        }
                    }
                    int[] data3=new int[k];
                    if (k >= 0) System.arraycopy(data2, 0, data3, 0, k);
                    LNH.setData1(data3);
                    DBUtils.release(conn1, state1, rs1);
                }

            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                DBUtils.release(conn, state, rs);
            }
            LNH.setHigh(highnum*1.0/(highnum+normalnum+lownum)+"");
            LNH.setLow(lownum*1.0/(highnum+normalnum+lownum)+"");
            LNH.setNormal(1-(highnum*1.0/(highnum+normalnum+lownum))-(lownum*1.0/(highnum+normalnum+lownum))+"");

            return LNH;
        }

    }
    public static class ContactDao {
        public ContactInfo[] select(String userid) throws Exception {
            Connection conn1 = null;
            Statement state1 = null;
            ResultSet rs1 = null;
            int k = 0, p = 0;
            try {
                conn1 = DBUtils.getConnect();
                state1 = conn1.createStatement();
                String sql = "select count(*) from emergency_contact,user where emergency_contact.userid=user.userid and user.userid='" + userid + "'";
                rs1 = state1.executeQuery(sql);


                while (rs1.next()) {
//                    if(k==3)break;
//                    data1[k++]= Integer.parseInt(rs.getString(3));
                    p = Integer.parseInt(rs1.getString(1));
//                    historyInfos[k++]=new HistoryInfo(rs1.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5));
                    //System.out.println(rs.getString(1) + " " + rs.getString(2));//就是输出第一列和第二列的值
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                DBUtils.release(conn1, state1, rs1);
            }
            if (p == 0) {
//                HistoryInfo[] historyInfos = new HistoryInfo[1];
//                historyInfos[0]=new HistoryInfo("暂无","暂无","暂无","暂无","暂无");
//                return historyInfos;
                return null;
            }
            Connection conn = null;
            Statement state = null;
            ResultSet rs = null;
            ContactInfo[] contactInfos = new ContactInfo[p];
            k = 0;
            try {
                conn = DBUtils.getConnect();
                state = conn.createStatement();
                String sql = "select * from emergency_contact,user where emergency_contact.userid=user.userid and user.userid='" + userid + "'";
                rs = state.executeQuery(sql);


                while (rs.next()) {
//                    if(k==3)break;
//                    data1[k++]= Integer.parseInt(rs.getString(3));
                    contactInfos[k++] = new ContactInfo(rs.getString(3), rs.getString(2), rs.getString(4));
                    //System.out.println(rs.getString(1) + " " + rs.getString(2));//就是输出第一列和第二列的值
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                DBUtils.release(conn, state, rs);
            }

            return contactInfos;
        }

        public String selectphone(int index, String userid) throws Exception {
            Connection conn = null;
            Statement state = null;
            ResultSet rs = null;
            String phone = "";
            try {
                conn = DBUtils.getConnect();
                state = conn.createStatement();
                String sql = "select phone from emergency_contact where userid='" + userid + "' ";

                rs = state.executeQuery(sql);
                int k = 0;

                while (rs.next()) {
                    if (k++ == index + 1) break;
                    phone = rs.getString(1);
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                DBUtils.release(conn, state, rs);
            }
            return phone;
        }

        public void update(String userid, int index, String contact_name, String relationship, String phone1) throws Exception {
            String phone = selectphone(index, userid);
            Connection conn = null;
            try {
                conn = DBUtils.getConnect();
                String sql = "update emergency_contact set contact_name=?,relationship=?,phone=? where userId='" + userid + "' and phone='" + phone + "'";
                PreparedStatement ps = conn.prepareStatement(sql);
                int k = 0;
                if (ps != null) {

                    //将数据插入数据库
                    ps.setString(1, contact_name);
                    ps.setString(2, relationship);
                    ps.setString(3, phone1);


                    // 执行sql查询语句并返回结果集
//                    int rs = ps.executeUpdate();
                }
            } catch (Exception e) {
                e.printStackTrace();

            }
        }

        public void insert(String userid,  String contact_name, String relationship, String phone) throws Exception {
            Connection conn = DBUtils.getConnect();
//                state = conn.createStatement();
            String sql = "insert into  emergency_contact  values (?,?,?,?);";
//                rs = state.executeQuery(sql);
            PreparedStatement pstm = conn.prepareStatement(sql);
            //通过setString给4个问好赋值，下面的course_id，user_id，course_time，us_job_id都是已有值的变量，不要误会了
            pstm.setString(2, contact_name);
            pstm.setString(1, userid);
            pstm.setString(3,relationship);
            pstm.setString(4,phone);
            //执行更新数据库
            pstm.executeUpdate();
            //关闭链接
            conn.close();
            //关闭访问
            pstm.close();
        }
    }

    public static class HistoryDao {

        //查询历史记录条
        public HistoryInfo[] select(String userid) throws Exception {
            Connection conn1 = null;
            Statement state1 = null;
            ResultSet rs1 = null;
            int k=0,p = 0;
            try {
                conn1 = DBUtils.getConnect();
                state1 = conn1.createStatement();
                String sql = "select count(*) from history_detail,history_list where history_list.historyid=history_detail.historyid and userid='"+userid+"' order by history_detail.historyid desc";
                rs1 = state1.executeQuery(sql);


                while (rs1.next()) {
//                    if(k==3)break;
//                    data1[k++]= Integer.parseInt(rs.getString(3));
                    p=Integer.parseInt(rs1.getString(1));
//                    historyInfos[k++]=new HistoryInfo(rs1.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5));
                    //System.out.println(rs.getString(1) + " " + rs.getString(2));//就是输出第一列和第二列的值
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                DBUtils.release(conn1, state1, rs1);
            }
            if(p==0){
//                HistoryInfo[] historyInfos = new HistoryInfo[1];
//                historyInfos[0]=new HistoryInfo("暂无","暂无","暂无","暂无","暂无");
//                return historyInfos;
                return null;
            }
            Connection conn = null;
            Statement state = null;
            ResultSet rs = null;
            HistoryInfo[] historyInfos = new HistoryInfo[p];
            k=0;
            try {
                conn = DBUtils.getConnect();
                state = conn.createStatement();
                String sql = "select date,status,score,distance,duration from history_detail,history_list where history_list.historyid=history_detail.historyid and userid='"+userid+"' order by history_detail.historyid desc";
                rs = state.executeQuery(sql);


                while (rs.next()) {
//                    if(k==3)break;
//                    data1[k++]= Integer.parseInt(rs.getString(3));
                    historyInfos[k++]=new HistoryInfo(rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5));
                    //System.out.println(rs.getString(1) + " " + rs.getString(2));//就是输出第一列和第二列的值
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                DBUtils.release(conn, state, rs);
            }

            return historyInfos;
        }

        public void insert(History history1,int historyid) throws Exception {
            Connection conn = DBUtils.getConnect();
//                state = conn.createStatement();
//            String sql = "insert into history_item (status,startAndEndTime,heart_avg,concentration,sysPres_avg,diaPres_avg,dangerousBehave,oxy_avg,historyid,drivingTemp,drivingWet,drivingCO2,drivingTVOC,date,score,distance,duration) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
            String sql="insert into history_detail  values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
//                rs = state.executeQuery(sql);
            PreparedStatement pstm = conn.prepareStatement(sql);
            //通过setString给4个问好赋值，下面的course_id，user_id，course_time，us_job_id都是已有值的变量，不要误会了
            pstm.setString(2, history1.getStatus());
            pstm.setString(3, history1.getStartAndEndTime());
            pstm.setString(4,history1.getHeart_avg());
            pstm.setString(5, history1.getConcentration());
            pstm.setString(6, history1.getSysPres_avg());
            pstm.setString(7, history1.getDiaPres_avg());
            pstm.setString(8, history1.getDangerousBehave());
            pstm.setString(9, history1.getOxy_avg());
            pstm.setString(1, historyid+"");
            pstm.setString(10, history1.getDrivingTemp());
            pstm.setString(11, history1.getDrivingWet());
            pstm.setString(12, history1.getDrivingCO2());
            pstm.setString(13, history1.getDrivingTVOC());
            pstm.setString(14, history1.getDate());
            pstm.setString(15, history1.getScore());
            pstm.setString(16, history1.getDistance());
            pstm.setString(17, history1.getDuration());
            //执行更新数据库
            pstm.executeUpdate();
            //关闭链接
            conn.close();
            //关闭访问
            pstm.close();
        }
    }

    public static class BehaveDao {
        public int[] select(int index,String userid) throws Exception {
            Connection conn = null;
            Statement state = null;
            ResultSet rs = null;
            UserDao userDao=new UserDao();

            String historyid=userDao.selectHistoryid(index,userid);
            int[] data=new int[6];

            try {
                conn = DBUtils.getConnect();
                state = conn.createStatement();
                String sql = "select * from dangerous_behave where historyid='"+historyid+"'";
                rs = state.executeQuery(sql);


                while (rs.next()) {
                    data[0]= Integer.parseInt(rs.getString(2));
                    data[1]= Integer.parseInt(rs.getString(3));
                    data[2]= Integer.parseInt(rs.getString(4));
                    data[3]= Integer.parseInt(rs.getString(5));
                    data[4]= Integer.parseInt(rs.getString(6));
                    data[5]= Integer.parseInt(rs.getString(7));
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                DBUtils.release(conn, state, rs);
            }
            return data;
        }

        public void insert(Behave behave, int historyid) throws Exception {
            Connection conn = DBUtils.getConnect();
//                state = conn.createStatement();
            String sql = "insert into  dangerous_behave  values (?,?,?,?,?,?,?,?,?,?);";
//                rs = state.executeQuery(sql);
            PreparedStatement pstm = conn.prepareStatement(sql);
            //通过setString给4个问好赋值，下面的course_id，user_id，course_time，us_job_id都是已有值的变量，不要误会了
            pstm.setString(2, behave.getCellphone());
            pstm.setString(3, behave.getYawning());
            pstm.setString(4,behave.getNot_buckling_up());
            pstm.setString(5, behave.getBoth_hands_leaving_wheel());
            pstm.setString(6, behave.getEyes_closed());
            pstm.setString(7, behave.getHead_lowered());
            pstm.setString(9, behave.getSmoke());
            pstm.setString(8, behave.getNot_facing_front());
            pstm.setString(10, behave.getNo_face_mask());
            pstm.setString(1, historyid+"");
            //执行更新数据库
            pstm.executeUpdate();
            //关闭链接
            conn.close();
            //关闭访问
            pstm.close();
        }
    }

    public static class WearDao {
        public void insert(Double d, Double d1, Double d2, Double d3, int i,int historyid) throws Exception {

            try {
                Connection conn = DBUtils.getConnect();
//                state = conn.createStatement();
                String sql = "insert into oxy (historyid,oxy,time) values (?,?,?);";
//                rs = state.executeQuery(sql);
                PreparedStatement pstm = conn.prepareStatement(sql);
                //通过setString给4个问好赋值，下面的course_id，user_id，course_time，us_job_id都是已有值的变量，不要误会了
                pstm.setString(1, historyid+"");
                pstm.setString(2,d+"");
                pstm.setString(3, i+"");
                //执行更新数据库
                pstm.executeUpdate();
                //关闭链接
                conn.close();
                //关闭访问
                pstm.close();
                Connection conn1 = DBUtils.getConnect();
//                state = conn.createStatement();
                String sql1 = "insert into diaPress (historyid,diaPress,time) values (?,?,?);";
//                rs = state.executeQuery(sql);
                PreparedStatement pstm1 = conn1.prepareStatement(sql1);
                //通过setString给4个问好赋值，下面的course_id，user_id，course_time，us_job_id都是已有值的变量，不要误会了
                pstm1.setString(1, historyid+"");
                pstm1.setString(2,d1+"");
                pstm1.setString(3, i+"");
                //执行更新数据库
                pstm1.executeUpdate();
                //关闭链接
                conn1.close();
                //关闭访问
                pstm1.close();
                Connection conn2 = DBUtils.getConnect();
//                state = conn.createStatement();
                String sql2 = "insert into heart (historyid,heart,time) values (?,?,?);";
//                rs = state.executeQuery(sql);
                PreparedStatement pstm2 = conn2.prepareStatement(sql2);
                //通过setString给4个问好赋值，下面的course_id，user_id，course_time，us_job_id都是已有值的变量，不要误会了
                pstm2.setString(1, historyid+"");
                pstm2.setString(2,d2+"");
                pstm2.setString(3, i+"");
                //执行更新数据库
                pstm2.executeUpdate();
                //关闭链接
                conn2.close();
                //关闭访问
                pstm2.close();

                Connection conn3 = DBUtils.getConnect();
//                state = conn.createStatement();
                String sql3 = "insert into sysPress (historyid,sysPress,time) values (?,?,?);";
//                rs = state.executeQuery(sql);
                PreparedStatement pstm3 = conn3.prepareStatement(sql3);
                //通过setString给4个问好赋值，下面的course_id，user_id，course_time，us_job_id都是已有值的变量，不要误会了
                pstm3.setString(1, historyid+"");
                pstm3.setString(2,d3+"");
                pstm3.setString(3, i+"");
                //执行更新数据库
                pstm3.executeUpdate();
                //关闭链接
                conn3.close();
                //关闭访问
                pstm3.close();


            } catch (Exception e) {
                e.printStackTrace();
            }
        }

    }
}
